package com.verisette.ncos.persistence.dao;

import java.util.List;

import org.corp.agi.persistence.dao.CommonDAOImpl;
import com.verisette.ncos.common.ProjectConstant;
import com.verisette.ncos.persistence.domain.CustomMasterUserGroup;
import com.verisette.ncos.persistence.domain.MasterObject;
import com.verisette.ncos.persistence.domain.MasterUserGroupToUserProfile;

public class AccessControlDAOImpl extends CommonDAOImpl implements AccessControlDAO {
	
	public List<MasterObject> listPrivilege(String username) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT MASTER_OBJECT.* ");
		sb.append("FROM MASTER_USER_GROUP ");
		sb.append("INNER JOIN MASTER_USER_USG_TO_USP ON MASTER_USER_GROUP.USG_ID = MASTER_USER_USG_TO_USP.USG_ID ");
		sb.append("INNER JOIN MASTER_USER_PROFILE ON MASTER_USER_USG_TO_USP.USP_ID = MASTER_USER_PROFILE.USP_ID ");
		sb.append("INNER JOIN MASTER_OBJECT_AUTH_BY_USG ON MASTER_OBJECT_AUTH_BY_USG.USG_ID = MASTER_USER_GROUP.USG_ID ");
		sb.append("INNER JOIN MASTER_OBJECT ON MASTER_OBJECT_AUTH_BY_USG.OBJ_ID = MASTER_OBJECT.OBJ_ID ");
		sb.append("WHERE MASTER_USER_PROFILE.USP_STATUS = 'A' ");
		sb.append("AND MASTER_USER_GROUP.USG_STATUS = 'A' ");
		sb.append("AND MASTER_USER_USG_TO_USP.UGP_STATUS = 'A' ");
		sb.append("AND MASTER_OBJECT_AUTH_BY_USG.OAG_ENABLE = 'T' ");
		sb.append("AND MASTER_OBJECT.OBJ_STATUS = 'A' ");
		sb.append("AND MASTER_USER_PROFILE.USP_USER_NAME = ? ");
		return (List<MasterObject>) nativeQuery(sb.toString(), MasterObject.class, username);
	}
	
	public List<MasterUserGroupToUserProfile> listUser(String username, String role) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT MASTER_USER_USG_TO_USP.* ");
	    sb.append("FROM MASTER_USER_GROUP ");
	    sb.append("INNER JOIN ");
	    sb.append("MASTER_USER_USG_TO_USP ");
	    sb.append("ON MASTER_USER_GROUP.USG_ID = MASTER_USER_USG_TO_USP.USG_ID ");
	    sb.append("INNER JOIN ");
	    sb.append("MASTER_USER_PROFILE ");
	    sb.append("ON MASTER_USER_USG_TO_USP.USP_ID = MASTER_USER_PROFILE.USP_ID ");
	    sb.append("WHERE (MASTER_USER_PROFILE.USP_USER_NAME = ?) ");
	    sb.append("AND(MASTER_USER_GROUP.USG_NAME = ?) ");
	    sb.append("AND(MASTER_USER_PROFILE.USP_STATUS = 'A') ");
	    sb.append("AND(MASTER_USER_GROUP.USG_STATUS = 'A') ");
	    sb.append("AND(MASTER_USER_USG_TO_USP.UGP_STATUS = 'A') ");
	    return (List<MasterUserGroupToUserProfile>) nativeQuery(sb.toString(), MasterUserGroupToUserProfile.class, username, role);
	}

	@Override
	public List<CustomMasterUserGroup> listUserGroupStatus(String username) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT MASTER_USER_PROFILE.USP_USER_NAME,");
		sb.append(" MASTER_USER_PROFILE.USP_ID,");
		sb.append(" MASTER_USER_USG_TO_USP.USG_ID,");
		sb.append(" MASTER_USER_GROUP.USG_NAME,");
		sb.append(" MASTER_USER_GROUP.USG_STATUS");
		sb.append(" FROM MASTER_USER_USG_TO_USP");
		sb.append(" INNER JOIN MASTER_USER_PROFILE ON MASTER_USER_USG_TO_USP.USP_ID = MASTER_USER_PROFILE.USP_ID");
		sb.append(" INNER JOIN MASTER_USER_GROUP ON MASTER_USER_USG_TO_USP.USG_ID = MASTER_USER_GROUP.USG_ID");
		sb.append(" WHERE (MASTER_USER_PROFILE.USP_USER_NAME = ?)");
		return (List<CustomMasterUserGroup>) nativeQuery(sb.toString(), CustomMasterUserGroup.class, username);
	}
	
	@Override
	public int getDestinationLogPasswordChange(String uspId) {
		int result;
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT max(running_number)");
		sb.append(" FROM log_pwd_change");
		sb.append(" WHERE usp_id = ?");
		try{
			result = (Short)nativeQuerySingleResult(sb.toString(), uspId);
		}catch (Exception e) {
			result = 0;
		}
		return result;
	}

	@Override
	public int getSourceLogPasswordChange(String encryptPwd, String uspId, int destination) {
		int result;
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT running_number");
		sb.append(" FROM log_pwd_change");
		sb.append(" WHERE change_password = ?");
		sb.append(" AND usp_id = ?");
		sb.append(" AND running_number>=");
		sb.append((destination-ProjectConstant.CHANGE_PWD_DUPLICATE));
		try{
			result = (Short)nativeQuerySingleResult(sb.toString(), encryptPwd, uspId);
		}catch (Exception e) {
			result = 0;
		}
		return result;
	}

}
